﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using Microsoft.LightSwitch;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;

namespace LightSwitchApplication.Server
{
    public class DataAccess
    {

        public static void UpdateAddress(string AddressId, string Latitude, string Longitude)
        {
            string connString = GetConnectionString();
            if (String.IsNullOrEmpty(connString)) return;
            if (String.IsNullOrEmpty(AddressId)) return;
            AddressId = StopInjection(AddressId);
            //Location = StopInjection(Location);
            Latitude = StopInjection(Latitude);
            Longitude = StopInjection(Longitude);
            #region Request DB
            using (SqlConnection conn = new SqlConnection(connString))
            {
                try
                {
                    conn.Open();
                    SqlCommand cmdGetData = new SqlCommand();
                    cmdGetData.Connection = conn;
                    cmdGetData.CommandType = CommandType.Text;
                    cmdGetData.CommandText = "UPDATE dbo.Addresses SET LATITUDE = isnull(@Latitude,LATITUDE), LONGITUDE = isnull(@Longitude,LONGITUDE) "
                        + "WHERE Id = @AddressId";
                    cmdGetData.Parameters.Add("AddressId", SqlDbType.VarChar, 20);
                    //cmdGetData.Parameters.Add("Location", SqlDbType.VarChar, 1000);
                    cmdGetData.Parameters.Add("Latitude", SqlDbType.Float);
                    cmdGetData.Parameters.Add("Longitude", SqlDbType.Float);
                    cmdGetData.Parameters["AddressId"].Value = AddressId;

                    //cmdGetData.Parameters["Location"].Value = Location;
                    cmdGetData.Parameters["Latitude"].Value = Latitude;
                    cmdGetData.Parameters["Longitude"].Value = Longitude;
                    cmdGetData.ExecuteNonQuery();
                }
                catch (SqlException)
                {
                    //Console.WriteLine(ex.Message);
                    //throw;
                }
                finally
                {
                    if (conn.State != ConnectionState.Closed) conn.Close();
                }

                //using (var dwspace = new DataWorkspace())
                //{
                //    Address address = dwspace.ApplicationData.Addresses.Where(add => add.Id.ToString() == AddressId).FirstOrDefault();
                //    if (address == null) return;
                //    address.Latitude = double.Parse(Latitude);
                //    address.Longitude = double.Parse(Longitude);
                //    dwspace.ApplicationData.SaveChanges();
                //}
            }
            #endregion
        }

        public static string GetSystemParameterValue(string ParameterName)
        {
            string result = string.Empty;
            string connString = GetConnectionString();
            if (String.IsNullOrEmpty(connString)) return result;
            if (String.IsNullOrEmpty(ParameterName)) return result;
            ParameterName = StopInjection(ParameterName);
            #region Request DB
            using (SqlConnection conn = new SqlConnection(connString))
            {
                try
                {
                    conn.Open();
                    SqlCommand cmdGetData = new SqlCommand();
                    cmdGetData.Connection = conn;
                    cmdGetData.CommandType = CommandType.Text;
                    cmdGetData.CommandText = "select top 1 Value from dbo.SystemParameters where Name = @Name";
                    cmdGetData.Parameters.Add("Name", SqlDbType.VarChar, 255);
                    cmdGetData.Parameters["Name"].Value = ParameterName;
                    result = (string)cmdGetData.ExecuteScalar();
                }
                catch (SqlException)
                {
                    //Console.WriteLine(ex.Message);
                    //throw;
                }
                finally
                {
                    if (conn.State != ConnectionState.Closed) conn.Close();
                }

            }
            #endregion
            return result;
        }


        private static string StopInjection(string value)
        {
            value = value.ToLower().Replace("delete", "");
            value = value.ToLower().Replace("drop", "");
            value = value.ToLower().Replace("update", "");
            value = value.ToLower().Replace("insert", "");
            value = value.ToLower().Replace("create", "");
            return value;
        }

        //private static string GetConnectionString()
        //{
        //    string result = System.Web.Configuration.WebConfigurationManager.ConnectionStrings["LightSwitchConnectionString"].ConnectionString;
        //}
        private static string GetConnectionString()
        {
            string result = ConfigurationManager.ConnectionStrings[1].ConnectionString; //"LocalSqlServer"
            return result;
        }
    }
}